Public Class frmSQLQuery
    Inherits System.Windows.Forms.Form

    'Public MyDataTable As clsDataTable
    Friend MyFullTableName As String
    Private WhereClause, JoinClause As String
    Private PriorWhere As String = "", PriorJoin As String = ""

    Friend Property FullTableName() As String
        Get
            Return MyFullTableName
        End Get
        Set(Value As String)
            MyFullTableName = Value
        End Set
    End Property

    Private Sub btnSelectAll_Click(sender As System.Object, e As System.EventArgs) Handles btnSelectAll.Click
        SelectAll()
    End Sub

    Private Sub SelectAll()
        PriorWhere = ""
        PriorJoin = ""
        lblCombine.Text = "Select all records in the current working table where..."
        cboCombine.Visible = False
        lstTables.SelectedIndex = 0
        lstFields.SelectedIndex = -1
        lstOp.SelectedIndex = -1
        cboCondition1.Text = ""
        cboOption.SelectedIndex = 0
        txtSQL.Text = StringFormat(FormatSQL(BuildSQL(), False)).Replace(vbCr, vbCrLf)
    End Sub

    Private Sub frmQuery_Load(sender As Object, e As System.EventArgs) Handles MyBase.Load
        'todo: set to last query in registry
        cboOption.SelectedIndex = 0
        lstTables.Items.Add("Current Browser")
        For i As clsProject.enumTableTypes = clsProject.enumTableTypes.Stations To clsProject.enumTableTypes.Tracking
            Select Case i
                Case clsProject.enumTableTypes.Stations To clsProject.enumTableTypes.RCodes, clsProject.enumTableTypes.StaGrp To clsProject.enumTableTypes.Tracking
                    lstTables.Items.Add(Project.TableName(i))
                Case clsProject.enumTableTypes.QCodes, clsProject.enumTableTypes.Branches
                    'dont add tables that only indirectly connect to data table (i.e., they are support tables for other support tables)
            End Select
        Next
        SelectAll()
        chkAvail.Visible = False 'so doesnt retrieve prior value
        'GetControlValues(REGAPP, Me)
        chkAvail.Visible = True
        chkAvail.Checked = Project.SelectAvailable
        cboCombine.SelectedIndex = 0
        cboOption.SelectedIndex = 0
    End Sub

    Private Function fixLikeWildcards(s As String) As String
        s = s.Replace("..", "%")
        s = s.Replace("*", "%")
        s = s.Replace("@", "_")
        s = s.Replace("?", "_")
        Return s
    End Function

    Friend Function BuildSQL() As String
        Try
            WhereClause = ""
            JoinClause = ""

            If lstTables.SelectedIndex <> -1 And lstFields.SelectedIndex <> -1 And lstOp.SelectedIndex <> -1 Then
                If Me.cboCombine.Visible Then WhereClause &= " " & Me.cboCombine.Text & " "

                WhereClause &= String.Format("{0}.{1} {2} ", IIf(lstTables.SelectedIndex = 0, "Dta", lstTables.Text), lstFields.Text, lstOp.Text)

                Dim Condition As String = ""

                Dim DataType As System.Type = Project.dsWRDB.Tables(IIf(lstTables.SelectedIndex = 0, "Working", lstTables.Text)).Columns(lstFields.Text).DataType
                Dim Quote As String = "'"

                Select Case lstOp.Text
                    Case "In", "Not In" 'put quotes around each value and enclose in parens (won't interpret dates)
                        Dim ar() As String = cboCondition1.Text.Split(",")
                        For Each item As String In ar
                            If Condition <> "" Then Condition &= ","
                            Condition &= Quote & item & Quote
                        Next
                        Condition = "(" & Condition & ")"
                    Case "Between", "Not Between"
                        If DataType Is GetType(Date) Then
                            Condition = String.Format("{0} and {1}", Project.DB.DateTime(cboCondition1.Text), Project.DB.DateTime(cboCondition2.Text))
                        ElseIf DataType Is GetType(Double) Or DataType Is GetType(Integer) Then
                            Dim dbl1 As Double = 0.0, dbl2 As Double = 0.0
                            Double.TryParse(cboCondition1.Text, dbl1)
                            Double.TryParse(cboCondition2.Text, dbl2)
                            Condition = String.Format(Globalization.CultureInfo.InvariantCulture, "{0} and {1}", dbl1, dbl2)
                        Else
                            Condition = String.Format("{0}{1}{0} and {0}{2}{0}", Quote, cboCondition1.Text, cboCondition2.Text)
                        End If
                    Case "Is Null", "Is Not Null"
                        Condition = ""
                    Case Else
                        If DataType Is GetType(Date) Then
                            Condition = Project.DB.DateTime(cboCondition1.Text)
                        ElseIf DataType Is GetType(Boolean) Then
                            Dim cond As String = "False"
                            If cboCondition1.Text.Replace("=", "").Trim.ToUpper.StartsWith("T") Or Val(cboCondition1.Text) = 1 Then cond = "True"
                            Condition = Project.DB.Logical(cond)
                        Else
                            If DataType Is GetType(Double) Or DataType Is GetType(Integer) Then
                                Dim dbl As Double = 0.0
                                Double.TryParse(cboCondition1.Text, dbl)
                                Condition = dbl.ToString(Globalization.CultureInfo.InvariantCulture)
                            Else
                                Condition = String.Format("{0}{1}{0}", Quote, cboCondition1.Text)
                            End If
                        End If
                End Select

                WhereClause &= Condition
            End If

            If WhereClause <> "" Then
                If PriorWhere = "" Then
                    WhereClause = " WHERE " & WhereClause
                Else
                    WhereClause = PriorWhere & WhereClause
                End If
            End If

            'figure out if a new join condition needs to be added

            If lstTables.SelectedIndex > 0 Then
                Dim KeyField As String = ""
                Select Case lstTables.Text
                    Case "Stations", "StaGrp" : KeyField = "Station_ID"
                    Case "PCodes", "PCodeGrp" : KeyField = "PCode"
                    Case "CCodes" : KeyField = "CCode"
                    Case "RCodes" : KeyField = "RCode"
                    Case "Tracking" : KeyField = "Track_ID"
                End Select
                If lstTables.Text = "Tracking" And ActiveTableType() = clsProject.enumTableTypes.Working Then
                    'is working table, need to join on two key fields
                    JoinClause = String.Format(" INNER JOIN {0} {1} ON {1}.Temp_DB_Name = '{2}' AND {1}.Master_DB_Name = '{3}'", Project.FullTableName(lstTables.Text), lstTables.Text, ActiveTableName, Project.MasterTableName)
                Else
                    JoinClause = String.Format(" INNER JOIN {0} {1} ON Dta.{2} = {1}.{2}", Project.FullTableName(lstTables.Text), lstTables.Text, KeyField)
                End If
            End If

            If PriorJoin.Contains(JoinClause) Then JoinClause = ""
            JoinClause = PriorJoin & JoinClause

            Return MainForm.SelectBuildQuery(Me.cboOption.SelectedIndex, JoinClause & WhereClause)
        Catch ex As Exception
            ErrorMsg(, ex)
            Return ""
        End Try
    End Function

    Private Sub btnClose_Click(sender As System.Object, e As System.EventArgs) Handles btnClose.Click
        Close()
    End Sub

    Private Sub btnExecute_Click(sender As Object, e As System.EventArgs) Handles btnExecute.Click
        Try
            'no longer prohibit this because sqlstring will contain delete statement automatically

            'Dim keywords As New Generic.List(Of String)
            'keywords.AddRange(txtSQL.Text.ToUpper.Split(" "))
            'If keywords.Contains("DROP") Or keywords.Contains("DELETE") Or keywords.Contains("UPDATE") Or keywords.Contains("INSERT") Or keywords.Contains("CREATE") Or keywords.Contains("ALTER") Then
            '    WarningMsg("You are only allowed to perform SELECT queries with this form.")
            '    Exit Sub
            'End If
            MainForm.SelectData(txtSQL.Text.Replace(vbCrLf, ""), cboOption.SelectedIndex, False)
            SaveControlValues(REGAPP, Me)
        Catch ex As Exception
            ErrorMsg(, ex)
        End Try
    End Sub

    Private Sub lstTables_SelectedIndexChanged(sender As Object, e As System.EventArgs) Handles lstTables.SelectedIndexChanged
        If lstTables.SelectedIndex = -1 Then Exit Sub
        lstFields.Items.Clear()
        For Each c As DataColumn In Project.dsWRDB.Tables(IIf(lstTables.SelectedIndex = 0, "Working", lstTables.Text)).Columns
            'If Not c.ColumnName.Equals("RecID", StringComparison.OrdinalIgnoreCase) Then lstFields.Items.Add(c.ColumnName)
            If Not c.Unique Then lstFields.Items.Add(c.ColumnName) 'don't show key fields (would cause unnecessary joins)
            'lstFields.Items.Add(c.ColumnName)
        Next
        lstOp.Items.Clear()
        cboCondition1.Items.Clear()
        cboCondition2.Items.Clear()
        If lstFields.Items.Count > 0 Then lstFields.SelectedIndex = 0
    End Sub

    Private Sub lstFields_SelectedIndexChanged(sender As Object, e As System.EventArgs) Handles lstFields.SelectedIndexChanged
        lstOp.Items.Clear()
        If lstFields.SelectedIndex = -1 Then Exit Sub
        Dim DataType As System.Type = Project.dsWRDB.Tables(IIf(lstTables.SelectedIndex = 0, "Working", lstTables.Text)).Columns(lstFields.Text).DataType
        If DataType Is GetType(String) Then
            lstOp.Items.AddRange(New String() {"Like", "In", "=", "<", "<=", ">", ">=", "Between", "Is Null", "Not Like", "Not In", "<>", "Not Between", "Is Not Null"})
        ElseIf DataType Is GetType(Boolean) Then
            lstOp.Items.AddRange(New String() {"=", "Is Null", "Is Not Null"})
        Else
            lstOp.Items.AddRange(New String() {"=", "<", "<=", ">", ">=", "Between", "Is Null", "<>", "Not Between", "Is Not Null"})
        End If
        lstOp.SelectedIndex = 0

        cboCondition1.Text = ""
        cboCondition2.Text = ""

        FillConditionLists()
    End Sub

    Private Sub chkAvail_Click(sender As Object, e As System.EventArgs) Handles chkAvail.Click
        FillConditionLists()
    End Sub

    Private Sub FillConditionLists()
        Try
            If lstTables.SelectedIndex = -1 Or lstFields.SelectedIndex = -1 Then Exit Sub

            Me.UseWaitCursor = True
            Application.DoEvents()

            'fill the combobox with available values
            Dim QueryTable, QueryAlias As String
            If chkAvail.Checked Then
                If lstTables.SelectedIndex = 0 Then
                    QueryTable = MyFullTableName & " Dta"
                    QueryAlias = "Dta"
                Else
                    Dim KeyField As String = ""
                    Select Case lstTables.Text
                        Case "Stations", "StaGrp" : KeyField = "Station_ID"
                        Case "PCodes", "PCodeGrp" : KeyField = "PCode"
                        Case "CCodes" : KeyField = "CCode"
                        Case "RCodes" : KeyField = "RCode"
                        Case "Tracking" : KeyField = "Track_ID"
                    End Select
                    QueryTable = String.Format("{0} INNER JOIN {1} Dta ON {0}.{2}=Dta.{2}", lstTables.Text, MyFullTableName, KeyField)
                    QueryAlias = lstTables.Text
                End If
            Else
                If lstTables.SelectedIndex = 0 Then
                    Select Case lstFields.Text.ToUpper
                        Case "STATION_ID" : QueryTable = "Stations"
                        Case "PCODE" : QueryTable = "PCodes"
                        Case "CCODE" : QueryTable = "CCodes"
                        Case "QCODE" : QueryTable = "QCodes"
                        Case "AGENCY" : QueryTable = "Agencies"
                        Case "LCODE", "SCODE" : QueryTable = "Validation"
                        Case Else
                            QueryTable = ""
                    End Select
                Else
                    QueryTable = lstTables.Text
                End If
                QueryAlias = QueryTable
            End If
            cboCondition1.Items.Clear()
            cboCondition2.Items.Clear()
            If QueryTable <> "" Then
                cboCondition1.Text = "Filling list..."
                cboCondition2.Text = "Filling list..."
                Application.DoEvents()
                Dim dr As DbDataReader = Nothing
                If QueryTable = "Validation" Then
                    dr = Project.DB.ExecuteReader($"SELECT DISTINCT {QueryAlias}.Valid_Value FROM {QueryTable} WHERE Field_Name='{lstFields.Text}' ORDER BY {QueryAlias}.Valid_Value")
                Else
                    dr = Project.DB.ExecuteReader($"SELECT DISTINCT {QueryAlias}.{lstFields.Text} FROM {QueryTable} ORDER BY {QueryAlias}.{lstFields.Text}")
                End If
                If dr Is Nothing Then Exit Sub
                While dr.Read
                    If Not dr.IsDBNull(0) Then
                        Dim s As String = ""
                        If TypeOf (dr.GetValue(0)) Is Date Then
                            s = dr.GetDateTime(0).ToString(MyDateTimeFormat)
                        Else
                            s = dr.GetValue(0)
                        End If
                        cboCondition1.Items.Add(s)
                        cboCondition2.Items.Add(s)
                        If cboCondition1.Items.Count > 1000 Then
                            cboCondition1.Items.Add("(There were too many items...)")
                            cboCondition2.Items.Add("(There were too many items...)")
                            Exit While
                        End If
                    End If
                End While
                dr.Close()
                cboCondition1.Text = ""
                cboCondition2.Text = ""
            End If
            Me.UseWaitCursor = False
        Catch ex As Exception
            ErrorMsg(, ex)
        End Try
    End Sub

    Private Sub lstOp_SelectedIndexChanged(sender As Object, e As System.EventArgs) Handles lstOp.SelectedIndexChanged
        lblCondition1.Visible = Not lstOp.Text.ToUpper.Contains("NULL")
        cboCondition1.Visible = Not lstOp.Text.ToUpper.Contains("NULL")
        lblCondition2.Visible = lstOp.Text.ToUpper.Contains("BETWEEN")
        cboCondition2.Visible = lstOp.Text.ToUpper.Contains("BETWEEN")
    End Sub

    Private Sub cboCondition_KeyPress(sender As Object, e As KeyPressEventArgs) Handles cboCondition1.KeyPress, cboCondition2.KeyPress
        If lstOp.Items(0) = "=" AndAlso e.KeyChar = "." Then 'is numeric field
            e.KeyChar = Globalization.CultureInfo.CurrentCulture.NumberFormat.NumberDecimalSeparator
        End If
    End Sub

    Private Sub cbo_Changed(sender As Object, e As System.EventArgs) Handles cboCondition1.Validated, cboCondition2.Validated, cboCondition1.SelectedIndexChanged, cboCondition2.SelectedIndexChanged, lstOp.SelectedIndexChanged, cboOption.SelectionChangeCommitted
        If lstOp.Text.Contains("Like") Then cboCondition1.Text = fixLikeWildcards(cboCondition1.Text)
        txtSQL.Text = StringFormat(FormatSQL(BuildSQL(), False)).Replace(vbCr, vbCrLf)
    End Sub

    Private Sub cboOption_Changed(sender As Object, e As System.EventArgs) Handles cboOption.SelectionChangeCommitted
        chkAvail.Enabled = cboOption.SelectedIndex = 0
    End Sub

    Private Sub btnAdd_Click(sender As System.Object, e As System.EventArgs) Handles btnAdd.Click
        PriorJoin = JoinClause
        PriorWhere = WhereClause
        lblCombine.Text = "Select all records based on earlier conditions..."
        cboCombine.Visible = True
        lstTables.SelectedIndex = 0
        lstFields.SelectedIndex = 0
        lstOp.SelectedIndex = 0
        cboCondition1.Text = ""
    End Sub

    Private Sub lnkSqlValid_Click(sender As Object, e As System.EventArgs) Handles lnkSqlValid.Click
        If Project.DB.IsSqlValid(txtSQL.Text) Then
            MessageBox.Show("The SQL string has a valid syntax.", "SQL Query")
        Else
            MessageBox.Show("The SQL string has an invalid syntax.", "SQL Syntax Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End If
    End Sub

    Private Sub btnSave_Click(sender As System.Object, e As System.EventArgs) Handles btnSave.Click
        frmInfoMsg.ShowInfoMsg("Parameter Queries", "You are about to save this query so it can be reused at a later time.\n\nNote that WRDB can also embed parameters in your queries to make it more convenient to reuse them. For each named parameter you place into your query enclosed in brackets (e.g., WHERE PCode = '{Enter Desired Parameter Code}'), a prompt will appear when you rerun the query which allows you to specify the desired value at that time.")
        With New SaveFileDialog
            .AddExtension = True
            .CheckPathExists = True
            .DefaultExt = "SQL"
            .SupportMultiDottedExtensions = False
            .Filter = "Saved SQL Query File (*.sql)|*.sql"
            .InitialDirectory = TestNull(Project.SQLDir, MyDataPath)
            If .ShowDialog(Me) = Windows.Forms.DialogResult.OK Then
                Dim sw As New IO.StreamWriter(.FileName, False)
                sw.Write(Me.txtSQL.Text)
                sw.Close()
                sw.Dispose()
                sw = Nothing
            End If
        End With
    End Sub

    Private Sub btnHelp_Click(sender As System.Object, e As System.EventArgs) Handles btnHelp.Click
        ShowHelp(Me, "15")
    End Sub

    Private Sub UpdateValidLink(SQL As String)
        If Project.DB.IsSqlValid(SQL) Then
            lnkSqlValid.Text = "Query syntax is valid"
            picSqlValid.Image = My.Resources.OK
            btnAdd.Enabled = True
        Else
            lnkSqlValid.Text = "Query syntax is invalid..."
            picSqlValid.Image = My.Resources.Critical
            btnAdd.Enabled = False
        End If
    End Sub

    Private Sub txtSQL_TextChanged(sender As Object, e As System.EventArgs) Handles txtSQL.TextChanged
        UpdateValidLink(txtSQL.Text)
    End Sub
End Class
